package com.xing.controller;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.web.bind.annotation.*;
import java.util.List;
import java.util.Map;

@RestController
@RequestMapping("/jdbc")
public class JdbcController {
    /**
     * Spring Boot 默认提供了数据源，默认提供了 org.springframework.jdbc.core.JdbcTemplate
     * JdbcTemplate 中会自己注入数据源，用于简化 JDBC操作
     * 还能避免一些常见的错误,使用起来也不用再自己来关闭数据库连接
     */
    @Autowired
    JdbcTemplate jdbcTemplate;

    //查询user表中所有数据
    //List 中的1个 Map 对应数据库的 1行数据
    //Map 中的 key 对应数据库的字段名，value 对应数据库的字段值
    @GetMapping("/list")
    public List<Map<String, Object>> userList(){
        String sql = "select * from user";
        List<Map<String,Object>> list = jdbcTemplate.queryForList(sql);
        System.out.println(list);
        return list;
    }

    //新增user
    @PostMapping("/add") //浏览器测试使用RequestMapping
    public String addUser(){
        String sql = "INSERT INTO user (id, name, pwd) VALUES (23,'小张','123678')";
        jdbcTemplate.update(sql);
        System.out.println("添加成功！");
        String sql1 = "select * from user";
        List<Map<String,Object>> list = jdbcTemplate.queryForList(sql1);
        System.out.println(list);
        return "addOk";
    }

    //更新user
    @PutMapping("/update/{id}") //浏览器测试使用RequestMapping
    public String updateUser(@PathVariable("id") int id){
        String sql = "update user set name=?,pwd=? where id="+id;
        //数据
        Object[] objects = new Object[2];//参数个数
        objects[0] = "小周";
        objects[1] = "369852";
        jdbcTemplate.update(sql,objects);
        System.out.println("更新成功！");
        return "updateOk";
    }

    //删除user
    @DeleteMapping("/delete/{id}") //浏览器测试使用RequestMapping
    public String deleteUser(@PathVariable("id") int id){
        String sql = "delete from user where id=?";
        System.out.println("删除成功！");
        return "deleteOk";
    }
}
//    DROP TABLE IF EXISTS `user`;
//        CREATE TABLE `user` (
//        `id` int NOT NULL AUTO_INCREMENT,
//        `name` varchar(50) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL,
//        `pwd` varchar(50) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL,
//        PRIMARY KEY (`id`) USING BTREE
//        ) ENGINE=InnoDB AUTO_INCREMENT=24 DEFAULT CHARSET=utf8mb3 ROW_FORMAT=DYNAMIC;
// INSERT INTO `user` VALUES(1,'小龙','123546'),(2,'aaa','bbb'),(3,'小明','3466'),(4,'小王','888'),(6,'Has','123321'),
// (7,'小王','12'),(9,'小Q','58779'),(10,'小W','5123'),(13,'小丽','12345'),(14,'小W','5123'),(15,'小W','5123'),
// (16,'小丽','12345'),(20,'小龙','123654'),(21,'小龙','123654'),(23,'小张','123678');
